package bg.cinema_system.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import bg.cinema_system.global.ConnectionJDBC;
import bg.cinema_system.pojo.Movie;
import bg.cinema_system.pojo.Projection;
import bg.cinema_system.pojo.User;

public class UserDAO {

	public static User isUserExist(String username, String password)
			throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		User user = new User();
		PreparedStatement prepareStatement = conn
				.prepareStatement("SELECT * FROM users WHERE username = ? AND password = md5(?) ");
		prepareStatement.setString(1, username);
		prepareStatement.setString(2, password);

		ResultSet rs = prepareStatement.executeQuery();
		rs.first();
		user.setUserId(rs.getInt("user_id"));
		user.setUsername(rs.getString("username"));
		user.setPassword(password);
		user.setEmail(rs.getString("email"));
		user.setRole(rs.getInt("fk_role_id"));

		prepareStatement.close();
		rs.close();
		conn.close();

		return user;
	}

	public static boolean createNewUser(User user) throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		boolean isUserExist = true;
		PreparedStatement prepareStatement = conn
				.prepareStatement("SELECT username FROM Users WHERE username = ?");
		prepareStatement.setString(1, user.getUsername());
		ResultSet rs = prepareStatement.executeQuery();
		isUserExist = rs.first();
		prepareStatement.close();
		rs.close();

		// If user doesn't exist then insert new one
		if (!isUserExist) {
			PreparedStatement prepareStatementInsert = conn
					.prepareStatement("INSERT INTO Users (username, password, email, fk_role_id) VALUES ( ?, md5(?), ?, 1)");
			prepareStatementInsert.setString(1, user.getUsername());
			prepareStatementInsert.setString(2, user.getPassword());
			prepareStatementInsert.setString(3, user.getEmail());

			// Execute insert SQL statement
			prepareStatementInsert.executeUpdate();
			prepareStatementInsert.close();
		}

		conn.close();
		return isUserExist;
	}

	public static boolean checkPassword(int userId, String password)
			throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement prepareStatement = conn
				.prepareStatement("SELECT users.password FROM users WHERE user_id = ? AND password = md5(?)");
		prepareStatement.setInt(1, userId);
		prepareStatement.setString(2, password);

		// Execute SELECT SQL statement
		ResultSet rs = prepareStatement.executeQuery();
		boolean checkResult = rs.first();

		prepareStatement.close();
		conn.close();

		return checkResult;
	}

	public static void changePassword(int userId, String password)
			throws SQLException {
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement prepareStatement = conn
				.prepareStatement("UPDATE Users SET password = md5(?) WHERE user_id = ?");
		prepareStatement.setString(1, password);
		prepareStatement.setInt(2, userId);

		// Execute update SQL statement
		prepareStatement.executeUpdate();
		prepareStatement.close();
		conn.close();
	}

	public static void insertMovie(Movie movie) throws SQLException, ParseException {
		Date date =  new SimpleDateFormat("HH:mm:ss").parse(movie.getDuration());
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement prepareStatement = conn
				.prepareStatement("INSERT INTO Movies (name, movie_type, duration, director, actors, country, summary) VALUES  (?, ?, ?, ?, ?, ?, ?)");
		prepareStatement.setString(1, movie.getName());
		prepareStatement.setString(2, movie.getMovieType());
		prepareStatement.setTime(3, new java.sql.Time(date.getTime()));
		prepareStatement.setString(4, movie.getDirector());
		prepareStatement.setString(5, movie.getActors());
		prepareStatement.setString(6, movie.getCountry());
		prepareStatement.setString(7, movie.getSummary());
		// Execute insert SQL statement
		prepareStatement.executeUpdate();
		prepareStatement.close();
		conn.close();
		
	}

	public static void insertProjection(Projection projection) throws SQLException, ParseException {
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement ps = conn.prepareStatement("select movie_id from movies where name = ?");
		ps.setString(1, projection.getMovieName());
		ResultSet rs = ps.executeQuery();
		rs.first();
		int movie_id = rs.getInt("movie_id");
		rs.close();
		ps.close();
		
		Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(projection.getTimeStart());
		PreparedStatement prepareStatement = conn
				.prepareStatement("INSERT INTO Projections (projection_start, fk_movie_id, fk_hall_id) VALUES (?, ?, ?)");
		prepareStatement.setTimestamp(1, new Timestamp(date.getTime()));
		prepareStatement.setInt(2, movie_id);
		prepareStatement.setInt(3, (Integer.valueOf(projection.getHallNumer())));
		// Execute insert SQL statement
		prepareStatement.executeUpdate();
		prepareStatement.close();
		conn.close();
		
	}
}
